import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;


/**
 * 实现增删改查
 * */

public class Main {
    public static void main(String[] args) throws Exception{

        /* 新建连接 */
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/class_test?useUnicode=true&characterEncoding=utf8","admin","admin");


        /**
         * 增
         * */

        String username = "20170350";
        int age = 100;
        Date birth = new  Date();
        // 新建需要增加的字段

        String insertSql = "INSERT INTO users(username,age,birth) VALUES (?,?,?);"; // 插入语句
        PreparedStatement insert = connection.prepareStatement(insertSql); // 预编译SQL语句

        /* 赋值 */
        insert.setString(1,username);
        insert.setInt(2,age);
        insert.setObject(3,birth);

        /* 执行update操作 update 可以用于update delete insert操作 */
        insert.executeUpdate();


        /**
         * 删 删除ID为2的一行数据
         * */

        String deleteSql = "DELETE FROM users WHERE id = '2';";
        PreparedStatement delete = connection.prepareStatement(deleteSql);

        delete.executeUpdate();


        /**
         * 改 修改id为2的用户名为Tom
         * */

        String updateSql = "UPDATE users SET username = 'Tom' WHERE id = '2';";
        PreparedStatement update = connection.prepareStatement(updateSql);

        update.executeUpdate();

        /**
         * 查 查询数据表中所有的id, username, age, birth
         * */


        String querySql = "SELECT id, username, age, birth FROM users;";
        PreparedStatement query = connection.prepareStatement(querySql);

        ResultSet resultSet = query.executeQuery();

        while (resultSet.next()){
            System.out.print(resultSet.getInt("id")+"\t");
            System.out.print(resultSet.getString("username")+"\t");
            System.out.print(resultSet.getInt("age")+"\t");
            System.out.print(resultSet.getObject("birth")+"\t");
            System.out.println();
        }
        connection.close();
    }
}
